******SET YOUR DIRECTORY to ...\replication


use "rawdata\County_and_City_Data_Book\CY1990.dta",clear

append using "rawdata\County_and_City_Data_Book\CY1980.dta"

append using "rawdata\County_and_City_Data_Book\CY1970.dta"

append using "rawdata\County_and_City_Data_Book\CY1960.dta"


**********dropping if only 1990
drop if place_s== ""

g sep = "-"
egen ID = concat(state_s sep place_s)
drop sep

g i = 1
sort ID year
bysort ID: egen check = total(i)
drop if check ==1
drop check i

g decade = year / 10
encode ID, g(num_ID)
tsset num_ID decade
tsfill, full

replace year = 1960 if decade==196
replace year = 1970 if decade==197
replace year = 1980 if decade==198
replace year = 1990 if decade==199
drop decade

g myear = -year
sort num_ID myear
bysort num_ID: replace place_FIPS = place_FIPS[_n-1] if year!=1990
bysort num_ID: replace name = name[_n-1] if year!=1990

drop myear

g i = (population!=.)
bysort num_ID: egen check = total(i)
drop if check <2
drop check i

drop GEOG8011 GEOG8012

sort num_ID year
bysort num_ID: replace GEOG8301 = GEOG8301[_N-1]

bysort num_ID: replace GEOG8302 = GEOG8302[_N-1]

bysort num_ID: replace GEOG8303 = GEOG8303[_N-1]

bysort num_ID: replace pl_name = pl_name[_n-1] if year == 1980 & pl_name[_n-1]!=""

bysort num_ID: replace state_s = state_s[_n+1] if state_s[_n+1]!=""
bysort num_ID: replace state_s = state_s[_n-1] if state_s[_n-1]!=""
bysort num_ID: replace state_s = state_s[_n+1] if state_s[_n+1]!=""
bysort num_ID: replace state_s = state_s[_n-1] if state_s[_n-1]!=""


bysort num_ID: replace pl_name = pl_name[_n+1] if pl_name[_n+1]!=""
bysort num_ID: replace pl_name = pl_name[_n-1] if pl_name[_n-1]!=""
bysort num_ID: replace pl_name = pl_name[_n+1] if pl_name[_n+1]!=""
bysort num_ID: replace pl_name = pl_name[_n-1] if pl_name[_n-1]!=""

**********change place_FIPS to match bridge
replace place_FIPS = "69120" if state_s == "01" & pl_name=="selma"
replace place_FIPS = "21070" if state_s == "05" & pl_name=="el dorado"
replace place_FIPS = "10345" if state_s == "06" & pl_name=="campbell"
replace place_FIPS = "35000" if state_s == "12" & pl_name=="jacksonville"
replace place_FIPS = "99003" if state_s == "15" & pl_name=="honolulu"
replace place_FIPS = "57732" if state_s == "17" & pl_name=="park forest"
replace place_FIPS = "33466" if state_s == "18" & pl_name=="highland"
replace place_FIPS = "60465" if state_s == "19" & pl_name=="ottumwa"
replace place_FIPS = "57575" if state_s == "20" & pl_name=="prairie village"
replace place_FIPS = "46027" if state_s == "21" & pl_name=="lexington-fayette"
replace place_FIPS = "02368" if state_s == "21" & pl_name=="ashland"
replace place_FIPS = "21325" if state_s == "24" & pl_name=="cumberland"
replace place_FIPS = "38180" if state_s == "26" & pl_name=="highland park"
replace place_FIPS = "14158" if state_s == "27" & pl_name=="crystal"
replace place_FIPS = "76720" if state_s == "28" & pl_name=="vicksburg"
replace place_FIPS = "15380" if state_s == "28" & pl_name=="columbus"
replace place_FIPS = "78154" if state_s == "29" & pl_name=="webster groves"
replace place_FIPS = "04695" if state_s == "34" & pl_name=="belleville"
replace place_FIPS = "05170" if state_s == "34" & pl_name=="bergenfield"
replace place_FIPS = "06260" if state_s == "34" & pl_name=="bloomfield"
replace place_FIPS = "34450" if state_s == "34" & pl_name=="irvington"
replace place_FIPS = "47500" if state_s == "34" & pl_name=="montclair"
replace place_FIPS = "53680" if state_s == "34" & pl_name=="nutley"
replace place_FIPS = "63000" if state_s == "34" & pl_name=="ridgewood"
replace place_FIPS = "79800" if state_s == "34" & pl_name=="west orange"
replace place_FIPS = "12150" if state_s == "35" & pl_name=="carlsbad"
replace place_FIPS = "40189" if state_s == "36" & pl_name=="lackawanna"
replace place_FIPS = "43082" if state_s == "36" & pl_name=="lockport"
replace place_FIPS = "63264" if state_s == "36" & pl_name=="rockville centre"
replace place_FIPS = "01420" if state_s == "39" & pl_name=="alliance"
replace place_FIPS = "09288" if state_s == "39" & pl_name=="brook park"
replace place_FIPS = "57386" if state_s == "39" & pl_name=="norwood"
replace place_FIPS = "64304" if state_s == "39" & pl_name=="portsmouth"
replace place_FIPS = "73264" if state_s == "39" & pl_name=="south euclid"
replace place_FIPS = "74608" if state_s == "39" & pl_name=="steubenville"
replace place_FIPS = "19900" if state_s == "40" & pl_name=="del city"
replace place_FIPS = "33408" if state_s == "42" & pl_name=="hazleton"
replace place_FIPS = "42168" if state_s == "42" & pl_name=="lebanon"
replace place_FIPS = "62416" if state_s == "42" & pl_name=="pottstown"
replace place_FIPS = "83512" if state_s == "42" & pl_name=="west mifflin"
replace place_FIPS = "00100" if state_s == "46" & pl_name=="aberdeen"
replace place_FIPS = "08236" if state_s == "48" & pl_name=="big spring"
replace place_FIPS = "67440" if state_s == "49" & pl_name=="sandy"
replace place_FIPS = "26452" if state_s == "54" & pl_name=="fairmont"
replace place_FIPS = "85156" if state_s == "54" & pl_name=="weirton"



save "rawdata\County_and_City_Data_Book\panel county city data book.dta", replace










use "intermediatedata\bridge_final.dta", replace

drop _merge

duplicates drop FSTATE FPLACE, force

save "rawdata\County_and_City_Data_Book\bridge for merge.dta", replace



use "rawdata\County_and_City_Data_Book\panel county city data book.dta", clear

destring place_FIPS, g(FPLACE)
destring state_s, g(FSTATE)

merge n:1 FSTATE FPLACE using "rawdata\County_and_City_Data_Book\bridge for merge.dta", keepusing(city_center FMSA)

drop if _merge==2

replace city_center = 0 if FSTATE==26 & pl_name=="east detroit"
replace FMSA = 2160 if FSTATE==26 & pl_name=="east detroit"

replace city_center = 0 if FSTATE==9 & pl_name=="milford"
replace FMSA = 1160 if FSTATE==9 & pl_name=="milford"

replace city_center = 0 if FSTATE==34 & pl_name=="orange"
replace FMSA = 5640 if FSTATE==34 & pl_name=="orange"

drop if FMSA == . 
drop if FMSA == 3320
drop if FMSA == 5380

drop _merge

replace population = 48081 if year==1970 & pl_name=="anchorage" & FMSA==380

replace population = 30218 if year==1960 & pl_name=="texarkana" & FMSA==8360

drop GEOG8301 GEOG8302 GEOG8303 persons_per_unit new_units rooms Mone_auto_perc state_num place_num detached_hu_perc

drop if rent==.

g income_cc = income if city_center==1
label variable income_cc "Median Income CC"

g birth_cc = birth if city_center==1
label variable birth_cc "Live Birth CC"

g pcthigh25_cc = pcthigh25 if city_center==1
label variable pcthigh25_cc "% 25Y+ with High School CC"

g housing_units_cc = housing_units if city_center==1
label variable housing_units_cc "Housing Units CC"

g occupied_units_cc = occupied_units if city_center==1
label variable occupied_units_cc "Occupied Units CC"

g house_value_cc = house_value if city_center==1
label variable house_value_cc "Single Family Median Value CC"

g rent_cc = rent if city_center==1
label variable rent_cc "Median Rent CC"

g rev_property_taxes_perc_cc = rev_property_taxes_perc if city_center==1
label variable rev_property_taxes_perc_cc "City Government Revenue % Property Taxes CC"

g expenses_highway_perc_cc = expenses_highway_perc if city_center==1
label variable expenses_highway_perc_cc "% Expenses Highway CC"

g expenses_educ_perc_cc = expenses_educ_perc if city_center==1
label variable expenses_educ_perc_cc "% Expenses Education CC"

g expenses_PW_perc_cc = expenses_PW_perc if city_center==1
label variable expenses_PW_perc_cc "% Public Welfare CC"

g expenses_health_perc_cc = expenses_health_perc if city_center==1
label variable expenses_health_perc_cc "% Public Health and Hospitals CC"

g expenses_pol_perc_cc = expenses_pol_perc if city_center==1
label variable expenses_pol_perc_cc "% Public Police CC"

g expenses_fire_perc_cc = expenses_fire_perc if city_center==1
label variable expenses_fire_perc_cc "% Public Fire CC"

g expenses_san_perc_cc = expenses_san_perc if city_center==1
label variable expenses_san_perc_cc "% Sanitation CC"

g expenses_PF_perc_cc = expenses_PF_perc if city_center==1
label variable expenses_PF_perc_cc "% Police and Fire CC"


g rev_property_taxes_cc = rev_property_taxes if city_center==1
label variable rev_property_taxes_cc "Revenue Property Taxes (1000$) CC"

g expenses_highway_cc = expenses_highway if city_center==1
label variable expenses_highway_cc "Expenses Highway (1000$) CC"

g expenses_educ_cc = expenses_educ if city_center==1
label variable expenses_educ_cc "Expenses Education (1000$) CC"

g expenses_PW_cc = expenses_PW if city_center==1
label variable expenses_PW_cc "Expenses Public Welfare (1000$) CC"

g expenses_health_cc = expenses_health if city_center==1
label variable expenses_health_cc "Expenses Health and Hospitals (1000$) CC"

g expenses_pol_cc = expenses_pol if city_center==1
label variable expenses_pol_cc "Expenses Police (1000$) CC"

g expenses_fire_cc = expenses_fire if city_center==1
label variable expenses_fire_cc "Expenses Fire (1000$) CC"

g expenses_san_cc = expenses_san  if city_center==1
label variable expenses_san_cc "Expenses Sanitation (1000$) CC"

g expenses_PF_cc = expenses_PF  if city_center==1
label variable expenses_PF_cc "Expenses Police and Fire (1000$) CC"

g unem_rate_CENSUS_cc = unem_rate_CENSUS if city_center==1
label variable unem_rate_CENSUS_cc "Unemployment Rate Census CC"

g unem_CENSUS_cc = unem_CENSUS if city_center==1
label variable unem_CENSUS_cc "Unemployed Census CC"


tostring FMSA, g(MSA_s)
tostring year, g(y_s)
g sep = "-"
egen MSA_y = concat(MSA_s sep y_s)



g i=1
bysort ID: egen check = total(i)
bysort FMSA: egen check_max = max(check) if city_center==0

drop if check < check_max & city_center==0

g wXvar = income * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g income_ncc = NUM / DEN
label variable income_ncc "Median Income NCC"
drop wXvar NUM DEN

g wXvar = birth * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g birth_ncc = NUM / DEN
label variable birth_ncc "Live Bith Income NCC"
drop wXvar NUM DEN

g wXvar = pcthigh25 * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g pcthigh25_ncc = NUM / DEN
label variable pcthigh25_ncc "% 25Y+ with High School NCC"
drop wXvar NUM DEN

g wXvar = housing_units * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g housing_units_ncc = NUM / DEN
label variable housing_units_ncc "Housing Units NCC"
drop wXvar NUM DEN

g wXvar = occupied_units * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g occupied_units_ncc = NUM / DEN
label variable occupied_units_ncc "Occupied Units NCC"
drop wXvar NUM DEN

g wXvar = house_value * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g house_value_ncc = NUM / DEN
label variable house_value_ncc "Single Family Median Value NCC"
drop wXvar NUM DEN

g wXvar = rent * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g rent_ncc = NUM / DEN
label variable rent_ncc "Median Rent NCC"
drop wXvar NUM DEN

g wXvar = rev_property_taxes_perc * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g rev_property_taxes_perc_ncc = NUM / DEN
label variable rev_property_taxes_perc_ncc "City Government Revenue % Property Taxes NCC"
drop wXvar NUM DEN

g wXvar = expenses_highway_perc * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_highway_perc_ncc = NUM / DEN
label variable expenses_highway_perc_ncc  "% Expenses Highway NCC"
drop wXvar NUM DEN


g wXvar = expenses_educ_perc * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_educ_perc_ncc = NUM / DEN
label variable expenses_educ_perc_ncc  "% Expenses Education NCC"
drop wXvar NUM DEN

g wXvar = expenses_PW_perc * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_PW_perc_ncc = NUM / DEN
label variable expenses_PW_perc_ncc  "% Public Welfare NCC "
drop wXvar NUM DEN

g wXvar = expenses_health_perc * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_health_perc_ncc = NUM / DEN
label variable expenses_health_perc_ncc  "% Health and Hospitals NCC "
drop wXvar NUM DEN

g wXvar = expenses_pol_perc * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_pol_perc_ncc = NUM / DEN
label variable expenses_pol_perc_ncc  "% Police NCC "
drop wXvar NUM DEN

g wXvar = expenses_fire_perc * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_fire_perc_ncc = NUM / DEN
label variable expenses_fire_perc_ncc  "% Fire NCC "
drop wXvar NUM DEN

g wXvar = expenses_san_perc * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_san_perc_ncc = NUM / DEN
label variable expenses_san_perc_ncc  "% Sanitation NCC "
drop wXvar NUM DEN

g wXvar = expenses_PF_perc * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_PF_perc_ncc = NUM / DEN
label variable expenses_PF_perc_ncc  "% Police and Fire NCC "
drop wXvar NUM DEN

g wXvar = rev_property_taxes * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g rev_property_taxes_ncc = NUM / DEN
label variable rev_property_taxes_ncc "Revenue Property Taxes (1000$) NCC"
drop wXvar NUM DEN

g wXvar = expenses_highway * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_highway_ncc = NUM / DEN
label variable expenses_highway_ncc "Expenses Highway (1000$) NCC"
drop wXvar NUM DEN

g wXvar = expenses_educ * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_educ_ncc = NUM / DEN
label variable expenses_educ_ncc "Expenses Education (1000$) NCC"
drop wXvar NUM DEN


g wXvar = expenses_PW * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_PW_ncc = NUM / DEN
label variable expenses_PW_ncc "Expenses Public Welfare (1000$) NCC"
drop wXvar NUM DEN

g wXvar = expenses_health * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_health_ncc = NUM / DEN
label variable expenses_health_ncc "Expenses Health and Hospitals (1000$) NCC"
drop wXvar NUM DEN

g wXvar = expenses_pol * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_pol_ncc = NUM / DEN
label variable expenses_pol_ncc "Police (1000$) NCC"
drop wXvar NUM DEN

g wXvar = expenses_fire * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_fire_ncc = NUM / DEN
label variable expenses_fire_ncc "Fire (1000$) NCC"
drop wXvar NUM DEN

g wXvar = expenses_san * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_san_ncc = NUM / DEN
label variable expenses_san_ncc "Sanitation (1000$) NCC"
drop wXvar NUM DEN

g wXvar = expenses_PF * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g expenses_PF_ncc = NUM / DEN
label variable expenses_PF_ncc "Police and Fire (1000$) NCC"
drop wXvar NUM DEN

g wXvar = unem_rate_CENSUS * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g unem_rate_CENSUS_ncc = NUM / DEN
label variable unem_rate_CENSUS_ncc "Unemployment Rate Census NCC"
drop wXvar NUM DEN

g wXvar = unem_CENSUS * population
bysort MSA_y: egen NUM = total(wXvar) if city_center==0
bysort MSA_y: egen DEN = total(population) if city_center==0
g unem_CENSUS_ncc = NUM / DEN
label variable unem_CENSUS_ncc "Unemployed Census NCC"
drop wXvar NUM DEN


sort FMSA year city_center
replace income_ncc = income_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace birth_ncc = birth_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace pcthigh25_ncc = pcthigh25_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace housing_units_ncc = housing_units_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace occupied_units_ncc = occupied_units_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace house_value_ncc = house_value_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace rent_ncc = rent_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace rev_property_taxes_perc_ncc = rev_property_taxes_perc_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace expenses_highway_perc_ncc = expenses_highway_perc_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace rev_property_taxes_ncc = rev_property_taxes_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace expenses_highway_ncc = expenses_highway_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace unem_rate_CENSUS_ncc = unem_rate_CENSUS_ncc[_n-1] if city_center[_n-1]==0 & city_center==1
replace unem_CENSUS_ncc = unem_CENSUS_ncc[_n-1] if city_center[_n-1]==0 & city_center==1


drop if city_center==0
drop sep MSA_y i check check_max population birth pcthigh25 income housing_units occupied_units house_value rent rev_property_taxes_perc expenses_highway_perc y_s rev_property_taxes expenses_highway unem_rate_CENSUS unem_CENSUS ID num_ID

save "intermediatedata\final county city data book.dta", replace







